import os
import time

from config.ServerApp import app
from config.Common import get_sqlite_db_path
from utils.SqliteExecutor import sqlite_execute_sql, sqlite_execute_query
import time
import os
import json
from config.LoadConfig import get_config

def __create_table():
    try:
        create_process_task_record_table_sql = '''
        CREATE TABLE IF NOT EXISTS process_task_record (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            batch_id text NOT NULL, -- 批次号，对应请求接口携带 
            gmt_create text,
            gmt_modify text,
            task_status text NOT NULL, -- 处理状态:INIT 待处理，PROCESSING 处理中，SUCCESS 处理成功，FAIL 处理失败
            remark text -- 处理结果备注
        );
        '''
        sqlite_execute_sql(create_process_task_record_table_sql)
        create_process_task_record_log_table_sql = '''
        CREATE TABLE IF NOT EXISTS process_task_record_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键ID
            task_record_id TEXT NOT NULL, -- 任务记录表Id
            unique_id TEXT NOT NULL, -- 处理数据对应uniqueId
            process_cost bigint default 0, --  comment '处理耗时，单位秒'
            process_target TEXT NOT NULL, -- 处理对象，例如：图片地址、文本等
            gmt_create TEXT, -- 创建时间
            gmt_modify TEXT, -- 最后修改时间
            process_status TEXT NOT NULL, -- 处理状态:INIT 待处理，PROCESSING 处理中，SUCCESS 处理成功，FAIL 处理失败
            remark TEXT, -- 处理结果备注
            process_result TEXT, -- 处理结果
            FOREIGN KEY (task_record_id) REFERENCES process_task_record (id) -- 外键约束，关联任务记录表的id
        );
        '''
        sqlite_execute_sql(create_process_task_record_log_table_sql)
    except Exception as e:
        print(e)


def db_inited():
    return os.path.exists(get_sqlite_db_path())

def init_db():
    # 初始化数据库
    app.ctx.logger.info("初始化数据库")
    __create_table()
    app.ctx.logger.info("初始化数据库完成!")



class db_version:

    def __init__(self, version: int, description: str, sqls: list):
        self.version: int = version
        self.description: str = description
        self.sqls: list = sqls
        pass


def sqlite_db_upgrade():
    """
    数据库ddl更新
    """
    insert_version_sql = "INSERT INTO version_info (version, description) VALUES (?, ?)"

    upgrade_list = __update_sqlite_ddl()
    if not upgrade_list:
        return

    for version_ddl in upgrade_list:
        # 执行ddl 执行完毕后 插入版本记录
        sql = ''
        try:
            app.ctx.logger.info(f"数据开始更新,版本:{version_ddl.version}")
            for sql in version_ddl.sqls:
                sqlite_execute_sql(sql)
            sqlite_execute_sql(insert_version_sql, (version_ddl.version, version_ddl.description))
            time.sleep(1)
        except Exception as e:
            app.ctx.logger.error(f"数据版本更新异常,版本:{version_ddl.version},sql:{sql},异常:{e}")
            raise e





def __get_current_version():
    try:
        version_query_sql = "SELECT version FROM version_info ORDER BY update_time DESC LIMIT 1"
        result = sqlite_execute_query(version_query_sql,())
        return result[0]['version'] if result else 0
    except Exception as e:
        print(e)
        return 0

def __update_sqlite_ddl():
    # 初始化版本表
    check_table_sql = """
          SELECT name FROM sqlite_master 
          WHERE type='table' AND name='version_info';
      """
    result = sqlite_execute_query(check_table_sql, ())
    if not result:
        create_version_info_table_sql = """
            CREATE TABLE `version_info` (
                version bigint NOT NULL PRIMARY KEY, -- 版本号
                description text NOT NULL, -- 版本注释
                update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP -- 更新时间
            );
        """
        sqlite_execute_sql(create_version_info_table_sql)
    _version = __get_current_version()

    result = []

    if _version < 1:
        result.append(db_version(1, '增加任务优先级', [
            """
            ALTER TABLE process_task_record ADD COLUMN priority int DEFAULT 9999999;
            """,
            """
            ALTER TABLE process_task_record ADD COLUMN ext_params text;        
            """,
            """
            ALTER TABLE process_task_record ADD COLUMN file_system_type text DEFAULT '';
            """
            ,
            """
            CREATE INDEX process_task_record_status_IDX ON process_task_record (task_status,priority,id);
            """
        ]))
    if _version < 2:
        result.append(db_version(2, '增加任务超时处理', [
            """
            ALTER TABLE process_task_record ADD COLUMN biz_type text;
            """,
            """
            UPDATE process_task_record set biz_type = 'compound_classify' where biz_type IS NULL;
            """
        ]))
    # if _version < 3:
    #     result.append(db_version(3, '新增一级类目表category_l1', [
    #         """
    #         CREATE TABLE IF NOT EXISTS category_l1 (
    #           id INTEGER PRIMARY KEY AUTOINCREMENT,
    #           name TEXT NOT NULL UNIQUE,
    #           alias_json TEXT NOT NULL DEFAULT '[]',
    #           status INTEGER NOT NULL DEFAULT 1,
    #           created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    #           updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    #         );
    #         """,
    #         """
    #         CREATE INDEX IF NOT EXISTS idx_category_l1_status ON category_l1(status);
    #         """
    #     ]))
    # if _version < 4:
    #     result.append(db_version(4, '新增二级类目表category_l2', [
    #         """
    #         CREATE TABLE IF NOT EXISTS category_l2 (
    #           id INTEGER PRIMARY KEY AUTOINCREMENT,
    #           l1_id INTEGER NOT NULL,
    #           name TEXT NOT NULL,
    #           alias_json TEXT NOT NULL DEFAULT '[]',
    #           status INTEGER NOT NULL DEFAULT 1,
    #           created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    #           updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    #           UNIQUE(l1_id, name),
    #           FOREIGN KEY(l1_id) REFERENCES category_l1(id)
    #         );
    #         """,
    #         """
    #         CREATE INDEX IF NOT EXISTS idx_category_l2_l1_status ON category_l2(l1_id, status);
    #         """
    #     ]))
    return result

def reset_job_status():
    sql = """
    update process_task_record set task_status = 'INIT' where  task_status = 'PROCESSING'
    """
    sqlite_execute_sql(sql)

__reset_task_status_sql = "update process_task_record set task_status = ?  where task_status = 'PROCESSING'"
def reset_task_record_status():
    """
    用于解决重启服务后 任务未执行完
    """
    ## 重置任务状态
    sqlite_execute_sql(__reset_task_status_sql, ('INIT',))

#
# def __resolve_categories_json_path(raw_path: str) -> str:
#     """
#     解析 categories.json 的实际路径：
#     1) 绝对路径直接用
#     2) 相对路径先以项目根目录为基准（本文件的上级上级目录）
#     3) 兜底以当前工作目录
#     """
#     if not raw_path:
#         return None
#     raw_path = raw_path.strip()
#     if os.path.isabs(raw_path):
#         return raw_path
#     # 项目根目录：当前文件所在目录的上级
#     current_dir = os.path.dirname(os.path.abspath(__file__))
#     project_root = os.path.dirname(current_dir)
#     candidate = os.path.join(project_root, raw_path)
#     if os.path.exists(candidate):
#         return candidate
#     # 兜底：相对当前工作目录
#     candidate = os.path.join(os.getcwd(), raw_path)
#     return candidate if os.path.exists(candidate) else None
#
#
# def seed_categories_if_needed():
#     """
#     启动时自动导入 categories.json 的类目数据（仅在空表或启用自动种子时执行，一次性幂等）。
#     配置项（[system]）：
#       - auto_seed_categories = true/false
#       - categories_json_path = categories.json
#     """
#     try:
#         system_conf = get_config('system')
#         auto_seed = str(system_conf.get('auto_seed_categories', 'false')).lower() == 'true'
#         json_path_cfg = system_conf.get('categories_json_path', 'categories.json')
#         json_path = __resolve_categories_json_path(json_path_cfg)
#
#         # 检查表是否存在与是否为空
#         l1_exists = sqlite_execute_query("SELECT COUNT(*) c FROM sqlite_master WHERE type='table' AND name='category_l1'", ())[0]['c'] == 1
#         l2_exists = sqlite_execute_query("SELECT COUNT(*) c FROM sqlite_master WHERE type='table' AND name='category_l2'", ())[0]['c'] == 1
#         if not (l1_exists and l2_exists):
#             return
#
#         l1_count = sqlite_execute_query("SELECT COUNT(*) c FROM category_l1", ())[0]['c']
#         l2_count = sqlite_execute_query("SELECT COUNT(*) c FROM category_l2", ())[0]['c']
#
#         if not auto_seed and (l1_count > 0 or l2_count > 0):
#             return
#         if not json_path or not os.path.exists(json_path):
#             app.ctx.logger.info(f"auto seed skipped: json not found -> {json_path_cfg}")
#             return
#
#         with open(json_path, 'r', encoding='utf-8') as f:
#             data = json.load(f)
#
#         # 幂等导入（存在则更新别名与启用状态；不存在则插入）
#         insert_l1_sql = "INSERT OR IGNORE INTO category_l1(name, alias_json, status) VALUES (?, ?, 1)"
#         update_l1_sql = "UPDATE category_l1 SET alias_json=?, status=1 WHERE name=?"
#         insert_l2_sql = (
#             "INSERT OR IGNORE INTO category_l2(l1_id, name, alias_json, status) "
#             "SELECT id, ?, ?, 1 FROM category_l1 WHERE name=?"
#         )
#         update_l2_sql = (
#             "UPDATE category_l2 SET alias_json=?, status=1 "
#             "WHERE name=? AND l1_id=(SELECT id FROM category_l1 WHERE name=?)"
#         )
#
#         l1_new = l2_new = 0
#         for l1 in data:
#             l1_name = l1.get('name', '').strip()
#             if not l1_name:
#                 continue
#             aliases = json.dumps(l1.get('aliases', []), ensure_ascii=False)
#             # 先尝试插入，不存在则新增
#             sqlite_execute_sql(insert_l1_sql, (l1_name, aliases))
#             # 再更新为启用/同步别名
#             sqlite_execute_sql(update_l1_sql, (aliases, l1_name))
#             l1_new += 1
#
#             for c in l1.get('children', []) or []:
#                 l2_name = (c.get('name') or '').strip()
#                 if not l2_name:
#                     continue
#                 l2_aliases = json.dumps(c.get('aliases', []), ensure_ascii=False)
#                 sqlite_execute_sql(insert_l2_sql, (l2_name, l2_aliases, l1_name))
#                 sqlite_execute_sql(update_l2_sql, (l2_aliases, l2_name, l1_name))
#                 l2_new += 1
#
#         app.ctx.logger.info(f"auto seed categories done: L1={l1_new}, L2={l2_new}, from={json_path}")
#     except Exception as e:
#         app.ctx.logger.warning(f"auto seed categories failed: {e}")

# just for test
if __name__ == '__main__':
    init_db()
    # 插入测试数据的SQL语句
    insert_sql = '''
    INSERT INTO process_task_record ( batch_id, gmt_create, gmt_modify, task_status, remark)
    VALUES ( ?, ?, ?, ?, ?)
    '''

    # 测试数据
    test_data = [
        ('123','2024-06-11 10:00:00', '2024-06-11 10:00:00', 'pending', 'First task'),
        ('123','2024-06-11 10:05:00', '2024-06-11 10:05:00', 'completed', 'Second task'),
        ('123','2024-06-11 10:10:00', '2024-06-11 10:10:00', 'failed', 'Third task'),
    ]
    data = ( 'testBatchId01', '2024-06-11 10:10:00', '2024-06-11 10:10:00', 'failed', 'Fourth task')
    id = sqlite_execute_sql(insert_sql,data)
    print(f"======>{id}")
    # batch_insert_data(insert_sql, test_data)

    query_sql = '''
    SELECT * FROM process_task_record
    WHERE task_status = ? AND batch_id = ?
    '''
    # 调用execute_query函数，并传入查询参数
    result = execute_query(query_sql, ('completed', 'batch1'))
    print(result)